package jsyf.oa.dao.report.wechatRelay;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.com.constant.WechatConstant;
import jsyf.com.util.DateUtils;
import jsyf.oa.entity.system.WechatShareEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * Created by y on 2018/4/4.
 */
@Repository
public class WechatDao extends BaseDao {
    public WechatDao() {
        super.iBaseEntity = new WechatShareEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, WechatShareEntity.class);
    }

    /**
     * 获取区域经理某天微信分享列表
     *
     * @param shareDate 分享日期
     * @param companyId 公司ID
     * @return
     */
    public List<JSONObject> getWechatShareList(String userId, String shareDate, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT");
        sql.append(" t.ID AS id,");
        sql.append(" t.COMPANY_ID AS companyId,");
        sql.append(" t.SHARE_DATE shareDate,");
        sql.append(" t.NAME AS name,");
        sql.append(" t.LINK_URL AS linkUrl,");
        sql.append(" t.IMAGE_URL AS imageUrl,");
        sql.append(" t.FORWARD_NUMBER AS forwardNumber,");
        sql.append(" t.CREATE_USER_ID AS createUserId,");
        sql.append(" t.CREATE_TIME AS createTime,");
        sql.append(" v.`NAME` AS createUserName,");
        sql.append(" t.REMARK AS remark,");
        sql.append(" (SELECT t_wechat_friends_contacts.SCREENSHOTS_URL AS img FROM t_wechat_friends_contacts WHERE left(t_wechat_friends_contacts.CREATE_TIME,10)=? AND USER_ID= ? ORDER BY CREATE_TIME DESC limit 0,1) as img,");
        sql.append(" (SELECT\n" +
                "\tIFNULL(t_wechat_friends_contacts.CONTACTS,0) as contact\n" +
                "FROM\n" +
                "\tt_wechat_friends_contacts\n" +
                "WHERE\n" +
                " USER_ID = ?" +
                " AND LEFT(CREATE_TIME,10)=? ORDER BY CREATE_TIME DESC LIMIT 0,1) AS contact,");
        sql.append("((SELECT IFNULL(t_wechat_friends_contacts.CONTACTS,0) FROM t_wechat_friends_contacts WHERE");
        sql.append(" USER_ID =? AND LEFT(CREATE_TIME,10)=? ORDER BY CREATE_TIME DESC LIMIT 0,1)-");
        sql.append(" (SELECT IFNULL(t_wechat_friends_contacts.CONTACTS,0) FROM t_wechat_friends_contacts WHERE");
        sql.append(" USER_ID =? AND LEFT(CREATE_TIME,10)=? ORDER BY CREATE_TIME DESC LIMIT 0,1)) as num");
        sql.append(" FROM");
        sql.append(" T_SYS_WECHAT_SHARE AS t LEFT JOIN V_SYS_USER AS v ON t.CREATE_USER_ID = v.ID");
        sql.append(" WHERE 1=1");
        sql.append(" AND t.SHARE_DATE=?");
        sql.append(" AND t.COMPANY_ID=?");
        return super.queryForJsonList(sql.toString(), new Object[]{shareDate, userId, userId, shareDate, userId, shareDate, userId, getYesterDay(shareDate), shareDate, companyId});
    }

    /**
     * 获取当天部门 个人微信汇ByUserId
     *
     * @param today     今天
     * @param companyId 公司Id
     * @param userId    用户Id
     * @return
     */
    public JSONObject getWechatShareToday(String today, String companyId, String userId, String roleName) {
        StringBuffer sql = new StringBuffer();
        Date currentTime = new Date();
        List<Object> sqlArgs = new ArrayList<Object>();
        Calendar c = Calendar.getInstance();
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum(wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum(wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))>0");
        sql.append(" THEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((sum(wechatcontacts.contacts)),0) AS contacts,");
        sql.append(" wechatcontacts.img");
        sql.append(" FROM (");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(today);
        sql.append(" FROM v_sys_user_for_report WHERE 1 = 1 AND (jobState <> 1 OR (jobState = 1 AND leaveDate > ?))");
        sqlArgs.add(today);
        sql.append(" AND ( userId =? ");
        sqlArgs.add(userId);
        sql.append(" )");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN ( SELECT count(1) as FinishForward,v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(v_sys_wechat_finish.createTime,'%Y-%m-%d')\n");
        sqlArgs.add(today);
        sql.append(" group by v_sys_wechat_finish.userId ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN(");
        sql.append(" SELECT");
        if (currentday.equals(today)) {
            //判断当前日期是否处于微信应转发时间，更改应转发条数
            if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                sql.append(" count(1)*0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate from v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?");
                sqlArgs.add(today);
                sqlArgs.add(companyId);
            } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                sql.append("  count(1)*1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate from v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ? ");
                sqlArgs.add(today);
                sqlArgs.add(companyId);
            }
        } else {//其他天数
            sql.append(" count(1)*1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate from v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?");
            sqlArgs.add(today);
            sqlArgs.add(companyId);
        }
        sql.append(" ) as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN(");
        sql.append(" select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.img ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS img");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    public JSONObject getForDepartTotalTodayByuserId(String today, String companyId, String userId) {
        StringBuffer sql = new StringBuffer();
        Date currentTime = new Date();
        List<Object> sqlArgs = new ArrayList<Object>();
        Calendar c = Calendar.getInstance();
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum(wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum(wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))>0");
        sql.append(" THEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((sum(wechatcontacts.contacts)),0) AS contacts");
        sql.append(" FROM (");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(today);
        sql.append(" FROM v_sys_user_for_report WHERE 1 = 1 AND (jobState <> 1 OR (jobState = 1 AND leaveDate > ?))");
        sqlArgs.add(today);
        sql.append("  AND (parentId =? OR ( userId in(");
        sql.append(" SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(");
        sql.append(" SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = ? )) AND v_sys_user_for_report.frontFlag = 1)");
        sqlArgs.add(userId);
        sqlArgs.add(userId);
        sql.append(" )");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN ( SELECT count(1) as FinishForward,v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(v_sys_wechat_finish.createTime,'%Y-%m-%d')\n");
        sqlArgs.add(today);
        sql.append(" group by v_sys_wechat_finish.userId ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN(");
        sql.append(" SELECT");
        if (currentday.equals(today)) {
            //判断当前日期是否处于微信应转发时间，更改应转发条数
            if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                sql.append(" count(1)*0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate from v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?");
                sqlArgs.add(today);
                sqlArgs.add(companyId);
            } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                sql.append("  count(1)*1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate from v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ? ");
                sqlArgs.add(today);
                sqlArgs.add(companyId);
            }
        } else {//其他天数
            sql.append(" count(1)*1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate from v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?");
            sqlArgs.add(today);
            sqlArgs.add(companyId);
        }
        sql.append(" ) as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN(");
        sql.append(" select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    public JSONObject getForDepartTotalMonthByuserId(String month, String companyId, String userId) {
        StringBuffer sql = new StringBuffer();
        Date currentTime = new Date();
        List<Object> sqlArgs = new ArrayList<Object>();
        Calendar c = Calendar.getInstance();
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum(wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum(wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))>0");
        sql.append(" THEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((sum(wechatcontacts.contacts)),0) AS contacts");
        sql.append(" FROM (");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(month);
        sql.append(" FROM v_sys_user_for_report WHERE 1 = 1 AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
        sqlArgs.add(month);
        sql.append("  AND (parentId =? OR ( userId in(");
        sql.append(" SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(");
        sql.append(" SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = ? )) AND v_sys_user_for_report.frontFlag = 1)");
        sqlArgs.add(userId);
        sqlArgs.add(userId);
        sql.append(" )");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN ( SELECT count(1) as FinishForward,v_sys_wechat_finish.userId AS userId");
        sql.append("  FROM v_sys_wechat_finish  WHERE 1 = 1 AND LEFT(v_sys_wechat_finish.createTime,7)= ?");
        sqlArgs.add(month);
        sql.append(" group by v_sys_wechat_finish.userId) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN(");
        sql.append(" SELECT");
        if (currentmonth.equals(month)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数

            if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-( SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID= ?)) as forward,");
                sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sqlArgs.add(currentday);
                sqlArgs.add(companyId);
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?");
                sqlArgs.add(month);
                sqlArgs.add(companyId);
            } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append(" from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?");
                sqlArgs.add(month);
                sqlArgs.add(companyId);
            }
        } else {//其他月份
            sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
            sql.append(" from v_sys_wechat_forward");
            sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?");
            sqlArgs.add(month);
            sqlArgs.add(companyId);
        }
        sql.append(" ) as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN (");
        sql.append(" select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 部门 个人微信汇ByUserId
     *
     * @param month
     * @param companyId
     * @param userId
     * @param roleName
     * @return
     */
    public JSONObject getWechatShareMonth(String month, String companyId, String userId, String roleName) {
        StringBuffer sql = new StringBuffer();
        Date currentTime = new Date();
        List<Object> sqlArgs = new ArrayList<Object>();
        Calendar c = Calendar.getInstance();
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum(wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum(wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))>0");
        sql.append(" THEN (IFNULL((sum(forward)), 0)-IFNULL((sum(finishForward)), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((sum(wechatcontacts.contacts)),0) AS contacts");
        sql.append(" FROM (");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(month);
        sql.append(" FROM v_sys_user_for_report WHERE 1 = 1 AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
        sqlArgs.add(month);
        sql.append(" AND ( userId =? ");
        sqlArgs.add(userId);
        sql.append(" )");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN ( SELECT count(1) as FinishForward,v_sys_wechat_finish.userId AS userId");
        sql.append("  FROM v_sys_wechat_finish  WHERE 1 = 1 AND LEFT(v_sys_wechat_finish.createTime,7)= ?");
        sqlArgs.add(month);
        sql.append(" group by v_sys_wechat_finish.userId) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN(");
        sql.append(" SELECT");
        if (currentmonth.equals(month)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数

            if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-( SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID= ?)) as forward,");
                sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sqlArgs.add(currentday);
                sqlArgs.add(companyId);
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?");
                sqlArgs.add(month);
                sqlArgs.add(companyId);
            } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append(" from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?");
                sqlArgs.add(month);
                sqlArgs.add(companyId);
            }
        } else {//其他月份
            sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
            sql.append(" from v_sys_wechat_forward");
            sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?");
            sqlArgs.add(month);
            sqlArgs.add(companyId);
        }
        sql.append(" ) as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN (");
        sql.append(" select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 部门微信报表首页
     *
     * @param
     * @return
     */
    public List<JSONObject> getForDepartByUserIdAndDateTimeToday(String dateTime, String companyId, String userId, String frontFlag) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" sysUser.userId as userId,");
        sql.append(" sysUser.departName AS departName,");
        sql.append(" sysUser.roleName AS roleName,");
        sql.append(" sysUser.userName AS userName,");
        sql.append(" sysUser.regionName AS regionName,");
        sql.append(" sysUser.reportDate AS reportDate,");
        sql.append(" IFNULL((wechatforward.forward), 0) AS forward,");
        sql.append(" IFNULL((wechatfinish.FinishForward), 0) AS finishForward,");
        sql.append(" CASE WHEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))>0");
        sql.append(" THEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((wechatcontacts.contacts),0) AS contacts");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
        sqlArgs.add(dateTime);
        if (frontFlag.equals("1")) {
            sql.append(" AND ( parentId = ? or userId = ?");
            sqlArgs.add(userId);
            sqlArgs.add(userId);
        } else {
            sql.append(" AND  ( userId in( SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(");
            sql.append(" SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = ?");
            sqlArgs.add(userId);
            sql.append(" )AND v_sys_user_for_report.frontFlag = 1 )");
        }
        sql.append(" AND companyId = '"+companyId+"'");
        sql.append(" AND v_sys_user_for_report.departBigName not in('服务部','财务部') ");
        sql.append(" )GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?) ");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("  COUNT(*) * 1 as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他天数
                sql.append("  COUNT(*) * 1 as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId= ?)");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID= ?)) as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(companyId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append(" from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append(" from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId= ?)");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 获取公司首页
     *
     * @param dateTime
     * @param userId
     * @return
     */
    public List<JSONObject> getForCompanyByUserIdAndDateTime(String dateTime, String userId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" sysUser.userId as userId,");
        sql.append(" sysUser.departName AS departName,");
        sql.append(" sysUser.roleName AS roleName,");
        sql.append(" sysUser.userName AS userName,");
        sql.append(" sysUser.regionName AS regionName,");
        sql.append(" sysUser.reportDate AS reportDate,");
        sql.append(" IFNULL((wechatforward.forward), 0) AS forward,");
        sql.append(" IFNULL((wechatfinish.FinishForward), 0) AS finishForward,");
        sql.append(" CASE WHEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))>0");
        sql.append(" THEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((wechatcontacts.contacts),0) AS contacts");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
            sqlArgs.add(dateTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND ( ( userId in( select userId from v_sys_user_for_report where companyId in(\n" +
                "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                "\t\t\t)\n" +
                "\t\t)\n" +
                ")");
        sqlArgs.add(userId);//departBigId
        sql.append(" )AND v_sys_user_for_report.frontFlag = 1 ))");
        sql.append(" AND v_sys_user_for_report.departBigName in('销售部','大客户部')");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                sql.append(" SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                        "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                        "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                        "\t\t\t)\n" +
                        "\t\t)");
                sql.append(" ))");
                sqlArgs.add(dateTime);
                sqlArgs.add(userId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID in(\n" +
                            "   SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)))\n" +
                            "\t\t) AS forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(userId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in(\n" +
                            "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") \n" +
                            ")");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in(\n" +
                            "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") \n" +
                            ")");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in(\n" +
                        "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                        "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                        "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                        "\t\t\t)\n" +
                        "\t\t)\n" +
                        ") \n" +
                        ")");
                sqlArgs.add(dateTime);
                sqlArgs.add(userId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId \n");
        sql.append(" order by departName,roleName desc");
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 获取公司微信汇总ByCompanyId
     *
     * @param dateTime
     * @param userId
     * @return
     */
    public JSONObject getWechatTotalforCompany(String dateTime, String userId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum( wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum( wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))>0");
        sql.append(" THEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL(sum(wechatcontacts.contacts),0) AS contacts,");
        sql.append(" sysUser.reportDate AS reportDate");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
            sqlArgs.add(dateTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND ( ( userId in( select userId from v_sys_user_for_report where companyId in(\n" +
                "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                "\t\t\t)\n" +
                "\t\t)\n" +
                ")");
        sqlArgs.add(userId);//departBigId
        sql.append(" )AND v_sys_user_for_report.frontFlag = 1 ))");
        sql.append(" AND v_sys_user_for_report.departBigName IN ('销售部', '大客户部')");
        sql.append(" ) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                sql.append(" SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                        "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                        "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                        "\t\t\t)\n" +
                        "\t\t)");
                sql.append(" ))");
                sqlArgs.add(dateTime);
                sqlArgs.add(userId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID in(\n" +
                            "   SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)))\n" +
                            "\t\t) AS forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(userId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in(\n" +
                            "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") \n" +
                            ")");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in(\n" +
                            "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                            "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                            "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") \n" +
                            ")");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(userId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in(\n" +
                        "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                        "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                        "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = ?\n" +
                        "\t\t\t)\n" +
                        "\t\t)\n" +
                        ") \n" +
                        ")");
                sqlArgs.add(dateTime);
                sqlArgs.add(userId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 获取部门首页BydepartId
     *
     * @param dateTime
     * @param departId
     * @return
     */
    public List<JSONObject> getForDepartBydepartId(String dateTime, String departId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" sysUser.userId as userId,");
        sql.append(" sysUser.departName AS departName,");
        sql.append(" sysUser.roleName AS roleName,");
        sql.append(" sysUser.userName AS userName,");
        sql.append(" sysUser.regionName AS regionName,");
        sql.append(" sysUser.reportDate AS reportDate,");
        sql.append(" IFNULL((wechatforward.forward), 0) AS forward,");
        sql.append(" IFNULL((wechatfinish.FinishForward), 0) AS finishForward,");
        sql.append(" CASE WHEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))>0");
        sql.append(" THEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((wechatcontacts.contacts),0) AS contacts");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
            sqlArgs.add(dateTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND ( ( userId in( select userId from v_sys_user_for_report where departBigId=? ");
        sql.append(" ");
        sqlArgs.add(departId);//departBigId
        sql.append(" )AND v_sys_user_for_report.frontFlag = 1 ))");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where departBigId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where departBigId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                sql.append(" select companyId from v_sys_user_for_report where departBigId=?");
                sql.append(" ))");
                sqlArgs.add(dateTime);
                sqlArgs.add(departId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID in(\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(departId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                        "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                        ") )");
                sqlArgs.add(dateTime);
                sqlArgs.add(departId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 获取集团首页
     *
     * @param dateTime
     * @param companyIds
     * @return
     */
    public List<JSONObject> getForGroupByUserIdAndDateTime(String dateTime, String companyIds) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String weekTime = "";
        if (dateTime.length() == 12) {
            weekTime = dateTime.substring(0, 10);
        }
        String first = getFirstDayOfWeek(weekTime, 1);
        String last = getFirstDayOfWeek(weekTime, 7);

        sql.append(" SELECT v_sys_user_for_report.companyId,v_sys_user_for_report.companyName,");
        sql.append(" IFNULL(sum( wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum( wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))>0");
        sql.append(" THEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))");
        sql.append(" ELSE 0 END noForward,? as reportDate,");
        if (dateTime.length() == 12) {
            sqlArgs.add(weekTime);
        } else {
            sqlArgs.add(dateTime);
        }
        sql.append(" IFNULL(sum(wechatcontacts.contacts),0) AS contacts");
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else if (dateTime.length() == 12) {
            sql.append(" DATE_FORMAT(\n" +
                    "\t\tv_sys_wechat_finish.createTime,\n" +
                    "\t\t'%Y-%m-%d'\n" +
                    "\t)");
            sql.append(" BETWEEN ? and ?");
            sqlArgs.add(first);
            sqlArgs.add(last);
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND v_sys_wechat_finish.companyId in(" + companyIds + ")");
        sql.append(" GROUP BY v_sys_wechat_finish.companyId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=v_sys_user_for_report.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(dateTime);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(currentday);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                sqlArgs.add(dateTime);
            }
        } else if (dateTime.length() == 12) {
            sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate BETWEEN ? and ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
            sqlArgs.add(first);
            sqlArgs.add(last);
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  sum(v_sys_wechat_forward.forwardNumber) - IFNULL(subwechat.minus,0) as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append(" LEFT JOIN( SELECT IFNULL( COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0)as minus,t_sys_wechat_share.COMPANY_ID as companyId");
                    sql.append(" FROM t_sys_wechat_share WHERE t_sys_wechat_share.SHARE_DATE =?");
                    sqlArgs.add(currentday);
                    sql.append(" AND COMPANY_ID IN (" + companyIds + ") GROUP BY t_sys_wechat_share.COMPANY_ID)");
                    sql.append(" AS subwechat on subwechat.companyId=v_sys_wechat_forward.companyId");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND v_sys_wechat_forward.companyId in (" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(dateTime);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(dateTime);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                sqlArgs.add(dateTime);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=v_sys_user_for_report.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=v_sys_user_for_report.userId");
        sql.append(" WHERE  v_sys_user_for_report.companyId IN(" + companyIds + ")");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?)) and v_sys_user_for_report.frontFlag=1");
            sqlArgs.add(dateTime);
        } else if (dateTime.length() == 12) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?)) and v_sys_user_for_report.frontFlag=1");
            sqlArgs.add(weekTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?)) and v_sys_user_for_report.frontFlag=1");
            sqlArgs.add(dateTime);
        }
        sql.append(" GROUP BY v_sys_user_for_report.companyId,v_sys_user_for_report.companyName ORDER BY companySortNo");
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 获取微信集团汇总
     *
     * @param dateTime
     * @param companyIds
     * @return
     */
    public JSONObject getForGroupTotal(String dateTime, String companyIds) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String weekTime = "";
        if (dateTime.length() == 12) {
            weekTime = dateTime.substring(0, 10);
        }
        String first = getFirstDayOfWeek(weekTime, 1);
        String last = getFirstDayOfWeek(weekTime, 7);

        sql.append(" SELECT v_sys_user_for_report.companyId,v_sys_user_for_report.companyName,");
        sql.append(" IFNULL(sum( wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum( wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))>0");
        sql.append(" THEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))");
        sql.append(" ELSE 0 END noForward,? as reportDate,");
        if (dateTime.length() == 12) {
            sqlArgs.add(weekTime);
        } else {
            sqlArgs.add(dateTime);
        }
        sql.append(" IFNULL(sum(wechatcontacts.contacts),0) AS contacts");
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else if (dateTime.length() == 12) {
            sql.append(" DATE_FORMAT(\n" +
                    "\t\tv_sys_wechat_finish.createTime,\n" +
                    "\t\t'%Y-%m-%d'\n" +
                    "\t)");
            sql.append(" BETWEEN ? and ?");
            sqlArgs.add(first);
            sqlArgs.add(last);
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND v_sys_wechat_finish.companyId in(" + companyIds + ")");
        sql.append(" GROUP BY v_sys_wechat_finish.companyId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=v_sys_user_for_report.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(dateTime);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(currentday);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                sqlArgs.add(dateTime);
            }
        } else if (dateTime.length() == 12) {
            sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate BETWEEN ? and ? AND companyId  in(" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
            sqlArgs.add(first);
            sqlArgs.add(last);
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  sum(v_sys_wechat_forward.forwardNumber) - IFNULL(subwechat.minus,0) as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append(" LEFT JOIN( SELECT IFNULL( COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0)as minus,t_sys_wechat_share.COMPANY_ID as companyId");
                    sql.append(" FROM t_sys_wechat_share WHERE t_sys_wechat_share.SHARE_DATE =?");
                    sqlArgs.add(currentday);
                    sql.append(" AND COMPANY_ID IN (" + companyIds + ") GROUP BY t_sys_wechat_share.COMPANY_ID)");
                    sql.append(" AS subwechat on subwechat.companyId=v_sys_wechat_forward.companyId");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND v_sys_wechat_forward.companyId in (" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(dateTime);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                    sqlArgs.add(dateTime);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (" + companyIds + ") GROUP BY v_sys_wechat_forward.companyId)");
                sqlArgs.add(dateTime);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=v_sys_user_for_report.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=v_sys_user_for_report.userId");
        sql.append(" WHERE  v_sys_user_for_report.companyId IN(" + companyIds + ")");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?)) and v_sys_user_for_report.frontFlag=1");
            sqlArgs.add(dateTime);
        } else if (dateTime.length() == 12) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?)) and v_sys_user_for_report.frontFlag=1");
            sqlArgs.add(weekTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?)) and v_sys_user_for_report.frontFlag=1");
            sqlArgs.add(dateTime);
        }
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 获取公司首页ByCompanyId
     *
     * @param dateTime
     * @param companyId
     * @return
     */
    public List<JSONObject> getForCompanyByCompanyId(String dateTime, String companyId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT v_sys_user_for_report.departBigId,\n" +
                "v_sys_user_for_report.departBigName,\n" +
                "IFNULL(sum( wechatforward.forward),0) as forward,\n" +
                "IFNULL(sum( wechatfinish.FinishForward),0) as finishForward,\n" +
                "CASE WHEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))>0\n" +
                "THEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))\n" +
                "ELSE 0 END noForward,\n" +
                " ? as reportDate,\n" +
                "IFNULL(sum(wechatcontacts.contacts),0) AS contacts\n" +
                " FROM v_sys_user_for_report");
        sqlArgs.add(dateTime);
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append("  AND v_sys_wechat_finish.companyId = ?");
        sqlArgs.add(companyId);
        sql.append(" GROUP BY v_sys_wechat_finish.userId ) as wechatfinish on wechatfinish.userId=v_sys_user_for_report.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND v_sys_wechat_forward.companyId = ?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND v_sys_wechat_forward.companyId = ?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND v_sys_wechat_forward.companyId = ?)");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum( v_sys_wechat_forward.forwardNumber) - ( SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID = ?)) as forward,");
                    sqlArgs.add(currentday);
                    sqlArgs.add(companyId);
                    sql.append("  v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7) = ? AND v_sys_wechat_forward.companyId =?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append(" from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7) = ? AND v_sys_wechat_forward.companyId =?)");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append(" from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7) = ? AND v_sys_wechat_forward.companyId =?)");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=v_sys_user_for_report.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=v_sys_user_for_report.userId");
        sql.append(" WHERE v_sys_user_for_report.companyId  = ?");
        sqlArgs.add(companyId);
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate > ?)) and v_sys_user_for_report.frontFlag=1");
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?)) and v_sys_user_for_report.frontFlag=1");
        }
        sql.append(" GROUP BY departBigId,departBigName,reportDate ORDER BY departSortNo");
        sqlArgs.add(dateTime);
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    /**
     * 部门微信转发汇总ByBigDepartId
     *
     * @param dateTime
     * @param departId
     * @return
     */
    public JSONObject getWechatTotalforDepart(String dateTime, String departId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum( wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum( wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))>0");
        sql.append(" THEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL(sum(wechatcontacts.contacts),0) AS contacts,\n");
        sql.append(" sysUser.reportDate AS reportDate");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
            sqlArgs.add(dateTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND ( ( userId in( select userId from v_sys_user_for_report where departBigId=? ");
        sqlArgs.add(departId);//departBigId
        sql.append(" )AND v_sys_user_for_report.frontFlag = 1 ))) AS sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where departBigId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where departBigId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                sql.append(" select companyId from v_sys_user_for_report where departBigId=?");
                sql.append(" ))");
                sqlArgs.add(dateTime);
                sqlArgs.add(departId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID in(\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(departId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(departId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                        "\t\t\t\tselect companyId from v_sys_user_for_report where departBigId= ?\n" +
                        ") )");
                sqlArgs.add(dateTime);
                sqlArgs.add(departId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    public List<JSONObject> getForDepartBycompanyId(String dateTime, String companyId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" sysUser.userId as userId,");
        sql.append(" sysUser.departName AS departName,");
        sql.append(" sysUser.roleName AS roleName,");
        sql.append(" sysUser.userName AS userName,");
        sql.append(" sysUser.regionName AS regionName,");
        sql.append(" sysUser.reportDate AS reportDate,");
        sql.append(" IFNULL((wechatforward.forward), 0) AS forward,");
        sql.append(" IFNULL((wechatfinish.FinishForward), 0) AS finishForward,");
        sql.append(" CASE WHEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))>0");
        sql.append(" THEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL((wechatcontacts.contacts),0) AS contacts");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
            sqlArgs.add(dateTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND ( ( userId in( select userId from v_sys_user_for_report where companyId=? ");
        sqlArgs.add(companyId);//departBigId
        sql.append(" )AND v_sys_user_for_report.frontFlag = 1 ))");
        sql.append(" AND v_sys_user_for_report.departBigName not in ('服务部','财务部')");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where companyId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where companyId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                sql.append(" select companyId from v_sys_user_for_report where companyId=?");
                sql.append(" ))");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID in(\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(companyId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                        "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                        ") )");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    public JSONObject getForDepartTotal(String dateTime, String companyId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        Date currentTime = new Date();
        String currentmonth = DateUtils.parse(c.getTime(), "yyyy-MM");
        String currentday = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" IFNULL(sum( wechatforward.forward),0) as forward,");
        sql.append(" IFNULL(sum( wechatfinish.FinishForward),0) as finishForward,");
        sql.append(" CASE WHEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))>0");
        sql.append(" THEN (IFNULL(sum( wechatforward.forward),0)-IFNULL(sum( wechatfinish.FinishForward),0))");
        sql.append(" ELSE 0 END noForward,");
        sql.append(" IFNULL(sum(wechatcontacts.contacts),0) AS contacts,");
        sql.append(" sysUser.reportDate AS reportDate");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,? as reportDate");
        sqlArgs.add(dateTime);
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");
        if (dateTime.length() == 10) {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >?))");
            sqlArgs.add(dateTime);
        } else {
            sql.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >=?))");
            sqlArgs.add(dateTime);
        }
        sql.append(" AND ( ( userId in( select userId from v_sys_user_for_report where companyId=? ");
        sqlArgs.add(companyId);//departBigId
        sql.append(" )AND v_sys_user_for_report.frontFlag = 1 ))");
        sql.append(" AND v_sys_user_for_report.departBigName not in ('服务部','财务部')");
        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");
        if (dateTime.length() == 10) {
            sql.append(" DATE_FORMAT( ?, '%Y-%m-%d') = DATE_FORMAT(");
            sqlArgs.add(dateTime);
            sql.append(" v_sys_wechat_finish.createTime,'%Y-%m-%d')");
        } else {
            sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= ?");
            sqlArgs.add(dateTime);
        }
        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");
        if (dateTime.length() == 10) {
            if (currentday.equals(dateTime)) {
                //判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("COUNT(*)* 0 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where companyId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                    sql.append(" select companyId from v_sys_user_for_report where companyId=?");
                    sql.append(" ))");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他天数
                sql.append("COUNT(*)* 1 AS forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate FROM v_sys_wechat_forward WHERE 1 = 1 AND shareDate= ? AND companyId  in( ");
                sql.append(" select companyId from v_sys_user_for_report where companyId=?");
                sql.append(" ))");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        } else {
            if (currentmonth.equals(dateTime)) {//当前月份，判断当前日期是否处于微信应转发时间，更改应转发条数
                if (DateUtils.isInDate(currentTime, WechatConstant.begiinFormat, WechatConstant.nightStart)) {
                    sql.append("  (sum(v_sys_wechat_forward.forwardNumber)-(SELECT IFNULL(COUNT(*) * t_sys_wechat_share.FORWARD_NUMBER,0) FROM t_sys_wechat_share WHERE  t_sys_wechat_share.SHARE_DATE= ? AND COMPANY_ID in(\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                            "\t\t\t)\n" +
                            "\t\t)\n" +
                            ") as forward,");
                    sql.append("   v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sqlArgs.add(currentday);
                    sqlArgs.add(companyId);
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                } else if (DateUtils.isInDate(currentTime, WechatConstant.nightStart, WechatConstant.closeFormat)) {
                    sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                    sql.append("   from v_sys_wechat_forward");
                    sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                            "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                            ") )");
                    sqlArgs.add(dateTime);
                    sqlArgs.add(companyId);
                }
            } else {//其他月份
                sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
                sql.append("   from v_sys_wechat_forward");
                sql.append("  WHERE 1 = 1 AND left(shareDate,7)= ? AND companyId in (\n" +
                        "\t\t\t\tselect companyId from v_sys_user_for_report where companyId= ?\n" +
                        ") )");
                sqlArgs.add(dateTime);
                sqlArgs.add(companyId);
            }
        }
        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");
        sql.append(" LEFT JOIN( select v_sys_wechat_contacts.createTime as createTime,");
        sql.append(" SUBSTRING_INDEX( group_concat( v_sys_wechat_contacts.CONTACTS ORDER BY v_sys_wechat_contacts.createTime DESC),',',1 ) AS contacts,");
        sql.append(" v_sys_wechat_contacts.userId as userId ");
        sql.append(" from v_sys_wechat_contacts");
        sql.append(" GROUP BY v_sys_wechat_contacts.userId");
        sql.append(" )as wechatcontacts  ON wechatcontacts.userId=sysUser.userId");
        return super.queryForJsonObject(sql.toString(), sqlArgs.toArray());
    }

    public static String getFirstDayOfWeek(String date, int day) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar cal = Calendar.getInstance();
        try {
            cal.setTime(sdf.parse(date));
            // Calendar默认周日为第一天, 所以设置为1
            cal.set(Calendar.DAY_OF_WEEK, day);
        } catch (Exception e) {

        }
        return sdf.format(cal.getTime());
    }

    public String getYesterDay(String dateTime) {
        //通过传过来的时间算出前一天
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = sdf.parse(dateTime);
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.DAY_OF_MONTH, -1);
            date = calendar.getTime();
        } catch (ParseException e) {
            e.printStackTrace();
        }

        return sdf.format(date);
    }

    public List<JSONObject> getTodayWechatShareList(String shareDate, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT");
        sql.append(" t.ID AS id,");
        sql.append(" t.COMPANY_ID AS companyId,");
        sql.append(" t.SHARE_DATE shareDate,");
        sql.append(" t.NAME AS name,");
        sql.append(" t.LINK_URL AS linkUrl,");
        sql.append(" t.IMAGE_URL AS imageUrl,");
        sql.append(" t.FORWARD_NUMBER AS forwardNumber,");
        sql.append(" t.CREATE_USER_ID AS createUserId,");
        sql.append(" t.CREATE_TIME AS createTime,");
        sql.append(" v.`NAME` AS createUserName,");
        sql.append(" t.REMARK AS remark");
        sql.append(" FROM");
        sql.append(" T_SYS_WECHAT_SHARE AS t LEFT JOIN V_SYS_USER AS v ON t.CREATE_USER_ID = v.ID");
        sql.append(" WHERE 1=1");
        sql.append(" AND t.SHARE_DATE=?");
        sql.append(" AND t.COMPANY_ID=?");
        return super.queryForJsonList(sql.toString(), new Object[]{shareDate, companyId});
    }

    public JSONObject getTodayWechatContact(String userId, String shareDate, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT left(CREATE_TIME,10) as createTime,");
        sql.append(" CONTACTS as contact,");
        sql.append(" t_wechat_friends_contacts.SCREENSHOTS_URL as img,");
        sql.append(" ((SELECT IFNULL(t_wechat_friends_contacts.CONTACTS,0) FROM t_wechat_friends_contacts WHERE");
        sql.append(" USER_ID =? AND LEFT(CREATE_TIME,10)=? ORDER BY CREATE_TIME DESC LIMIT 0,1)-");
        sql.append(" (SELECT IFNULL(t_wechat_friends_contacts.CONTACTS,0) FROM t_wechat_friends_contacts WHERE");
        sql.append(" USER_ID =? AND LEFT(CREATE_TIME,10)<? ORDER BY CREATE_TIME DESC LIMIT 0,1)) as num");
        sql.append(" FROM t_wechat_friends_contacts WHERE t_wechat_friends_contacts.USER_ID = ? AND left(t_wechat_friends_contacts.CREATE_TIME,10)=?");
        return super.queryForJsonObject(sql.toString(), new Object[]{userId, shareDate, userId, shareDate, userId, shareDate});
    }
   public JSONObject checkIsLeave(String  userId, String shareDate){
        //判断是否请假
       StringBuffer sql=new StringBuffer();
       sql.append(" SELECT");
       sql.append(" ID FROM t_sys_leave WHERE ? BETWEEN t_sys_leave.BEGIN_TIME");
       sql.append(" AND t_sys_leave.END_TIME AND t_sys_leave.USER_ID =?");
       sql.append(" GROUP BY t_sys_leave.USER_ID");
       return  super.queryForJsonObject(sql.toString(),shareDate,userId);
   }

   public List<JSONObject> getWechatBhg( String userIds,String dateTime){
        StringBuffer sql=new StringBuffer();
        sql.append("SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\twecharShare.userId,\n" +
                "\t\t\tIFNULL(wecharShare.num, 0) - IFNULL(shaerDetail.num, 0) AS num\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tv_sys_user.id AS userId,\n" +
                "\t\t\t\t\tCOUNT(1) AS num\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tv_sys_user\n" +
                "\t\t\t\tLEFT JOIN t_sys_wechat_share ON t_sys_wechat_share.COMPANY_ID = v_sys_user.companyId\n" +
                "\t\t\t\tAND t_sys_wechat_share.SHARE_DATE = '"+dateTime+"'\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_sys_wechat_share.COMPANY_ID\n" +
                "\t\t\t\tAND v_sys_user.jobState != 1\n" +
                "\t\t\t\tAND v_sys_user.id IN ("+userIds+")\n" +
                "\t\t\t\tAND v_sys_user.main = 1\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tv_sys_user.id\n" +
                "\t\t\t) AS wecharShare\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_wechat_share_detail.CREATE_USER_ID AS userId,\n" +
                "\t\t\t\tCOUNT(1) AS num\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_wechat_share_detail\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_sys_wechat_share_detail.DEL_FLAG = 0\n" +
                "\t\t\tAND LEFT (\n" +
                "\t\t\t\tt_sys_wechat_share_detail.CREATE_TIME,\n" +
                "\t\t\t\t10\n" +
                "\t\t\t) = '"+dateTime+"'\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_sys_wechat_share_detail.CREATE_USER_ID\n" +
                "\t\t) AS shaerDetail ON wecharShare.userId = shaerDetail.userId\n" +
                "\t) AS obj\n" +
                "WHERE\n" +
                "\tobj.num > 0");
       return super.queryForJsonList(sql.toString());
   }
    public String getdate(int i) // //获取前后日期 i为正数 向后推迟i天，负数时向前提前i天
    {
        Date dat = null;
        Calendar cd = Calendar.getInstance();
        cd.add(Calendar.DATE, -i);
        dat = cd.getTime();
        SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
        return dformat.format(dat);
    }
}
